from django_redis import get_redis_connection
from django.core.cache import cache
import json
from utils.getPublicData import *
from myApp.models import *
from utils.querymysql import querymysql


def get_cached_query(cache_key, query, params=None):
    """专门用于缓存SQL查询结果的包装器"""
    try:
        cached_data = cache.get(cache_key)
        if cached_data is not None:
            return cached_data

        # 执行SQL查询
        data = querymysql(query, params or [], 'select')
        cache.set(cache_key, data)
        return data
    except Exception as e:
        print(f"Redis缓存错误: {e}")
        return querymysql(query, params or [], 'select')


def getIndexData():
    """首页图表数据 - 纯数据处理，不需要缓存"""
    # 这些函数内部已经有缓存机制
    averageCity = list(getaverageCity()[:11])
    averageCityX = [x['city'] for x in averageCity]
    averageCityY = [round(float(x['avg_max_salary']), 1) for x in averageCity]

    salaryCategoryList = list(getsalaryCategory())
    salaryCategoryData = [{'name': i['salaryCategory'], 'value': i['count']} for i in salaryCategoryList]

    expSalaryList = list(getexpSalary())
    expSalaryX = [x['workExperience'] for x in expSalaryList]
    expSalaryY1 = [round(float(x['avg_max_salary']), 1) for x in expSalaryList]
    expSalaryY2 = [round(float(x['avg_min_salary']), 1) for x in expSalaryList]

    peopleCategoryList = list(getpeopleCategory())
    peopleCategoryData = [{'name': i['people_category'], 'value': i['count']} for i in peopleCategoryList]

    addressSumList = list(getaddressSum())
    addressSumData = [{'name': i['city'], 'value': i['count']} for i in addressSumList]

    # 获取并处理salary_top数据
    salary_top = list(getsalaryTop())
    # 将字典转换为列表格式
    sorted_arr = []
    for item in salary_top:
        # 将字典的值转换为列表
        sorted_arr.append([v for v in item.values()])

    return (averageCityX, averageCityY, salaryCategoryData, expSalaryX,
            expSalaryY1, expSalaryY2, peopleCategoryData, addressSumData,
            sorted_arr)


def getUserHistory(userInfo):
    """获取用户历史记录"""
    # 从Django数据库获取历史记录
    dataList = list(History.objects.filter(user=userInfo).order_by('-count'))
    jobIdList = [i.jobId for i in dataList]

    # 获取职位详情（使用缓存）
    jobList = []
    for job_id in jobIdList:
        cache_key = f'job_data_{job_id}'
        query = 'SELECT * FROM jobData WHERE id = %s'
        params = [int(job_id)]
        job_data = get_cached_query(cache_key, query, params)[0]
        # 将字典值转换为列表
        jobList.append([v for v in job_data.values()])

    return jobList


def addHistoryData(userInfo, jobId):
    """添加历史记录 - 写操作，不需要缓存"""
    hisData = History.objects.filter(user=userInfo, jobId=jobId)
    if len(hisData):
        hisData[0].count += 1
        hisData[0].save()
    else:
        History.objects.create(user=userInfo, jobId=jobId)


def changePwd(userInfo, passwordInfo):
    """修改密码 - 写操作，不需要缓存"""
    oldPwd = passwordInfo['oldPassword']
    newPwd = passwordInfo['newPassword']
    chkPwd = passwordInfo['chkPassword']

    user = User.objects.get(username=userInfo.username)
    if oldPwd != user.password:
        return '原密码错误'
    if newPwd != chkPwd:
        return '两次密码不一致'

    user.password = newPwd
    user.save()
    return None


def get_job_details(job_id):
    """获取职位详情"""
    cache_key = f'job_data_{job_id}'
    query = 'SELECT * FROM jobData WHERE id = %s'
    params = [int(job_id)]
    job_data = get_cached_query(cache_key, query, params)[0]
    # 将字典值转换为列表
    return [v for v in job_data.values()]


def clear_user_cache(user_id):
    """清除用户相关的所有缓存"""
    patterns = [
        f'user_history_{user_id}',
        f'user_history_count_{user_id}',
        f'user_recommendations_{user_id}',
        f'user_behavior_{user_id}',
        f'user_preferences_{user_id}',
        f'user_job_data_{user_id}_*',
        f'user_job_details_{user_id}_*'
    ]
    for pattern in patterns:
        keys = cache.keys(pattern)
        if keys:
            cache.delete_many(keys)